1 Public Class FrmCatADD
2 Dim stockID As Integer
3 Dim edit As Boolean
4 Dim xlstcat As Boolean
5 Dim xlstitem As Boolean
6 Private Sub FrmCatADD_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
7 Me.Text = "Category"
8 End Sub
9
10 Private Sub FrmCatADD_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
11 xlstcat = False
12 xlstitem = False
13 lstitems.Items.Clear()
14 ' If Split(Me.Text, " - ")(1) = "Edit" Then
15 ' stockID = globalID
16 ' sqlSTR = "SELECT * FROM tbl_Category_File WHERE catg_ID =" & stockID
17 ' ExecuteSQLQuery(sqlSTR)
18 ' If sqlDT.Rows.Count > 0 Then
19 ' txtid.Text = sqlDT.Rows(0)("Catg_ID")
20 ' txtname.Text = sqlDT.Rows(0)("Catg_Name")
21 ' txtdesc.Text = sqlDT.Rows(0)("Catg_Description")
22 ' End If
23 ' Else
24 ' txtid.Text = ""
25 ' txtname.Text = ""
26 ' txtdesc.Text = ""
27 ' End If
28 GroupBox1.Enabled = True
29 GroupBox2.Enabled = True
30 GroupBox3.Enabled = False
31 FILLComboBox2("SELECT ID, Description FROM TBL_Unit_Measure", cmbUnit)
32 FILLComboBox("SELECT Group_ID, Group_Description FROM TBL_Group", cmbGroup)
33 'FILLComboBox("SELECT Catg_ID, Catg_Name FROM TBL_Category_File", cmbCategory)
34 lstcat.Enabled = True
35 sqlSTR = "SELECT * FROM TBL_Group"
36 ExecuteSQLQuery(sqlSTR)
37 If sqlDT.Rows.Count > 0 Then
38 cmbGroup.Text = sqlDT.Rows(0)("Group_ID") & " - " & R_Change(sqlDT.Rows(0)("Group_Description"))
39 FillListView(ExecuteSQLQuery("SELECT Catg_ID as 'ID', Catg_Name as 'Name', Catg_Description as 'Description' " & _
40 "FROM TBL_Category_File " & _
41 " WHERE Group_ID =" & Split(cmbGroup.Text, " - ")(0)), lstcat, 0)
42 cmdadd.Enabled = True
43 cmdEdit.Enabled = True
44 Else
45 cmdadd.Enabled = False
46 cmdEdit.Enabled = False
47 End If
48 End Sub
49
50 Private Sub cmdcancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdcancel.Click
51 cmdadd.Enabled = True
52 cmdEdit.Enabled = True
53 GroupBox1.Enabled = True
54 GroupBox2.Enabled = True
55 'GroupBox3.Enabled = True
56 grpCat.Visible = False
57 edit = False
58 End Sub
59
60 Private Sub cmdsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdsave.Click
61 'MsgBox(edit)
62 If txtname.Text = "" Then
63 MsgBox("No Category Name Please Input", MsgBoxStyle.Exclamation, "Sales and Inventory")
64 Exit Sub
65 End If
66 If txtdesc.Text = "" Then
67 MsgBox("No Category Description Please Input", MsgBoxStyle.Exclamation, "Sales and Inventory")
68 Exit Sub
69 End If
70 If edit = True Then
71 sqlSTR = "UPDATE TBL_Category_File SET Catg_Name ='" & txtname.Text & "', " _
72 & "Catg_Description ='" & R_eplace(txtdesc.Text) & "', " _
73 & "Group_ID =" & Split(cmbGroup.Text, " - ")(0) & " WHERE catg_ID =" & txtid.Text
74 ExecuteSQLQuery(sqlSTR)
75 Audit_Trail(xUser_ID, TimeOfDay, "Edit Data On Category Listing ")
76 Else
77 sqlSTR = "INSERT INTO TBL_Category_File (Catg_Name, Catg_Description, Group_ID) VALUES ('" & txtname.Text & "', " _
78 & "'" & R_eplace(txtdesc.Text) & "', " & _
79 Split(cmbGroup.Text, " - ")(0) & ")"
80 ExecuteSQLQuery(sqlSTR)
81 Audit_Trail(xUser_ID, TimeOfDay, "Add New Data On Category Listing ")
82 End If
83 'ExecuteSQLQuery(sqlSTR)
84 MsgBox("Record Successfuly Update!", MsgBoxStyle.Information)
85 FillListView(ExecuteSQLQuery("SELECT Catg_ID as 'Category ID', Catg_Name as 'Category Name', Catg_Description as 'Category Description' FROM tbl_Category_File"), FrmCatList.lstCat, 0)
86 'FillListView(ExecuteSQLQuery("SELECT Catg_ID as 'ID', Catg_Name as 'Name', Catg_Description as 'Description' FROM tbl_Category_File"), lstcat, 0)
87 FillListView(ExecuteSQLQuery("SELECT Catg_ID as 'ID', Catg_Name as 'Name', Catg_Description as 'Description' " & _
88 "FROM TBL_Category_File " & _
89 " WHERE Group_ID =" & Split(cmbGroup.Text, " - ")(0)), lstcat, 0)
90 'FILLComboBox("SELECT Catg_ID, Catg_Name FROM TBL_Category_File", cmbCategory)
91 FILLComboBox("SELECT Catg_ID, Catg_Name " & _
92 "FROM TBL_Category_File" & _
93 " WHERE Group_ID =" & Split(cmbGroup.Text, " - ")(0), cmbCategory)
94 GroupBox1.Enabled = True
95 GroupBox2.Enabled = True
96 GroupBox3.Enabled = False
97 grpCat.Visible = False
98 ' cmdadd.Enabled = True
99 ' cmdEdit.Enabled = True
100 edit = False
101 'Me.Close()
102 End Sub
103
104 Private Sub cmdadd2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdadd2.Click
105 If lstcat.Items.Count > 0 Then
106 lstcat.Focus()
107 edit = False
108 GroupBox3.Enabled = True
109 GroupBox2.Enabled = False
110 GroupBox1.Enabled = False
111 txtitemID.Text = ""
112 txtItemName.Text = ""
113 txtItemDesc.Text = ""
114 txtItemBarcode.Text = ""
115 txtItemprice.Text = ""
116 txtItemreorder.Text = ""
117 If cmbCategory.Text = "" Then
118 FILLComboBox("SELECT Catg_ID, Catg_Name " & _
119 "FROM TBL_Category_File" & _
120 " WHERE Group_ID =" & Split(cmbGroup.Text, " - ")(0), cmbCategory)
121 End If
122 lstcat.Enabled = False
123 End If
124 End Sub
125
126 Private Sub cmdSave2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave2.Click
127 Dim msg As String, msgERR As String
128 Dim duplicate_barcode As Boolean
129 Dim duplicate_item_number As Boolean
130 Dim tmp_barcode As Integer
131 msg = "Please Complete The Following " & Chr(10) & " --------------------------- "
132 msgERR = msg
133 If txtItemName.Text = "" Then
134 msg = msg & Chr(10) & " Item Name : < Should not be blank >"
135 End If
136 If txtItemDesc.Text = "" Then
137 msg = msg & Chr(10) & " Item Description : < Should not be blank > "
138 End If
139 If cmbUnit.Text = "" Then
140 msg = msg & Chr(10) & " Item Measure : < Should not be blank > "
141 End If
142 If Not IsNumeric(txtItemBarcode.Text) Then
143 msg = msg & Chr(10) & " Barcode: < Should be numeric >"
144 End If
145
146 If Not IsNumeric(txtItemreorder.Text) Then
147 msg = msg & Chr(10) & " Reorder Point: < Should be numeric >"
148 End If
149
150 If Not IsNumeric(txtItemprice.Text) Then
151 msg = msg & Chr(10) & " Price : < Should be numeric >"
152 End If
153
154 If Len(txtItemBarcode.Text) < 4 Then
155 msg = msg & Chr(10) & " Barcode : < Barcode minimum length should be 4 >"
156 End If
157
158 If msg <> msgERR Then
159 MsgBox(msg, MsgBoxStyle.Information, "Sales and Inventory")
160 Exit Sub
161 End If
162 If Len(txtItemBarcode.Text) > 13 Then
163 MsgBox("Barcode length should not be lower than 13 !! ", MsgBoxStyle.Exclamation, "Sales and Inventory")
164 Exit Sub
165 End If
166
167 If edit = True Then
168 sqlSTR = "SELECT * FROM TBL_Category_Item_File WHERE Item_ID =" & txtitemID.Text
169 ExecuteSQLQuery(sqlSTR)
170 tmp_barcode = sqlDT.Rows(0)("Item_Barcode")
171 If R_Change(sqlDT.Rows(0)("Item_Description")) = txtItemDesc.Text Then
172 duplicate_item_number = False
173 Else
174 sqlSTR = "SELECT * FROM TBL_Category_Item_File WHERE Item_Description ='" & txtItemDesc.Text & "'"
175 ExecuteSQLQuery(sqlSTR)
176 If sqlDT.Rows.Count > 0 Then
177 MsgBox("Item Number Already Exist !!", MsgBoxStyle.Exclamation, "Sales and Inventory")
178 duplicate_item_number = True
179 Exit Sub
180 Else
181 duplicate_item_number = False
182 End If
183 End If
184
185 If tmp_barcode = txtItemBarcode.Text Then
186 duplicate_barcode = False
187 Else
188 'Find if barcode already exist
189 sqlSTR = "SELECT * FROM TBL_Category_Item_File WHERE Item_Barcode =" & txtItemBarcode.Text
190 ExecuteSQLQuery(sqlSTR)
191 If sqlDT.Rows.Count > 0 Then
192 MsgBox("Barcode Already Exist !!", MsgBoxStyle.Exclamation, "Sales and Inventory")
193 duplicate_barcode = True
194 Exit Sub
195 Else
196 duplicate_barcode = False
197 End If
198 End If
199 If Not duplicate_barcode And Not duplicate_item_number Then
200 sqlSTR = "UPDATE TBL_Category_Item_File SET Item_Name ='" & R_eplace(txtItemName.Text) & "', " _
201 & "Catg_ID =" & Split(cmbCategory.Text, " - ")(0) & ", " _
202 & "Item_Description ='" & txtItemDesc.Text & "', " _
203 & "Item_Barcode =" & txtItemBarcode.Text & ", " _
204 & "Item_Reorder_Point =" & txtItemreorder.Text & ", " _
205 & "Item_Org_Price =" & CDbl(txtItemprice.Text) & ", " _
206 & "Unit_Measure='" & cmbUnit.Text & "', " _
207 & "Item_Price =" & CDbl(txtItemprice.Text) + (CDbl(txtItemprice.Text) * (VAT / 100)) _
208 & " WHERE Item_ID =" & txtitemID.Text
209 ExecuteSQLQuery(sqlSTR)
210 Audit_Trail(xUser_ID, TimeOfDay, "Edit Item File List")
211 End If
212 Else
213 'ADD
214 sqlSTR = "SELECT * FROM TBL_Category_Item_File WHERE Item_Description ='" & txtItemDesc.Text & "'"
215 ExecuteSQLQuery(sqlSTR)
216 If sqlDT.Rows.Count > 0 Then
217 MsgBox("Item Number Already Exist !!", MsgBoxStyle.Exclamation, "Sales and Inventory")
218 Exit Sub
219 End If
220
221 sqlSTR = "SELECT * FROM TBL_Category_Item_File WHERE Item_Barcode =" & txtItemBarcode.Text
222 ExecuteSQLQuery(sqlSTR)
223 If sqlDT.Rows.Count > 0 Then
224 MsgBox("Barcode already exists !!", MsgBoxStyle.Exclamation, "Sales and Inventory")
225 Exit Sub
226 End If
227 'MsgBox("here")
228 sqlSTR = "INSERT INTO tbl_Category_Item_File (Catg_ID, Item_Name, Item_Description, Item_Barcode, Item_Reorder_Point, Item_Org_Price, Unit_Measure, Item_Price) VALUES(" & Split(cmbCategory.Text, " - ")(0) & ", " _
229 & "'" & R_eplace(txtItemName.Text) & "', " _
230 & "'" & txtItemDesc.Text & "', " _
231 & txtItemBarcode.Text & ", " _
232 & txtItemreorder.Text & ", " _
233 & CDbl(txtItemprice.Text) & ", " _
234 & "'" & cmbUnit.Text & "', " _
235 & CDbl(txtItemprice.Text) + (CDbl(txtItemprice.Text) * (VAT / 100)) & ")"
236 ExecuteSQLQuery(sqlSTR)
237 Audit_Trail(xUser_ID, TimeOfDay, "Add New Item File List")
238 End If
239
240 FillListView(ExecuteSQLQuery("SELECT Item_ID as 'Item ID', replace(Replace(Item_Name,'$.$',''''),'$..$',',') as 'Item Name', Item_Description as 'Description / Item Number' FROM tbl_Category_Item_File WHERE Catg_ID =" & lstcat.FocusedItem.Text), lstitems, 1)
241 GroupBox1.Enabled = True
242 GroupBox2.Enabled = True
243 GroupBox3.Enabled = False
244 edit = False
245 lstcat.Enabled = True
246 End Sub
247
248
249 Private Sub cmdcancel2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdcancel2.Click
250 GroupBox1.Enabled = True
251 GroupBox2.Enabled = True
252 GroupBox3.Enabled = False
253 edit = False
254 lstcat.Enabled = True
255 End Sub
256
257 Private Sub lstitems_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lstitems.Click
258 ' MsgBox("A")
259 'xlstitem = True
260 'xlstcat = False
261 sqlSTR = "SELECT * FROM TBL_Category_Item_File WHERE Item_ID =" & lstitems.FocusedItem.Text
262 ExecuteSQLQuery(sqlSTR)
263 If sqlDT.Rows.Count > 0 Then
264 txtitemID.Text = sqlDT.Rows(0)("Item_ID")
265 txtItemName.Text = R_Change(sqlDT.Rows(0)("Item_Name"))
266 txtItemDesc.Text = sqlDT.Rows(0)("Item_Description")
267 txtItemBarcode.Text = sqlDT.Rows(0)("Item_Barcode")
268 txtItemprice.Text = sqlDT.Rows(0)("Item_Org_Price")
269 cmbUnit.SelectedItem = (sqlDT.Rows(0)("Unit_Measure"))
270 'cmbsupplier.SelectedItem = sqlDT.Rows(0)("Supp_ID") & " - " & sqlDT.Rows(0)("Suppname")
271 txtItemreorder.Text = sqlDT.Rows(0)("Item_Reorder_Point")
272 End If
273 End Sub
274
275 Private Sub cmdedit2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdedit2.Click
276 If lstitems.Items.Count > 0 Then
277 lstitems.Focus()
278 lstitems.Select()
279 lstcat.Enabled = False
280 edit = True
281 GroupBox1.Enabled = False
282 GroupBox3.Enabled = True
283 End If
284 End Sub
285
286 Private Sub txtItemprice_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtItemprice.TextChanged
287 txtItemprice.Text = str_Filter(txtItemprice, 48, 57, 46, 1)
288 If txtItemprice.Text = "0" Or txtItemprice.Text = "" Then txtItemprice.Text = "1"
289 End Sub
290
291 Private Sub txtItemreorder_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtItemreorder.TextChanged
292 txtItemreorder.Text = str_Filter(txtItemreorder, 48, 57, 0, 0)
293 End Sub
294
295 Private Sub txtItemBarcode_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtItemBarcode.TextChanged
296 txtItemBarcode.Text = str_Filter(txtItemBarcode, 48, 57, 0, 0)
297 End Sub
298
299 Private Sub lstcat_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lstcat.Click
300 'xlstcat = True
301 'xlstitem = False
302 FillListView(ExecuteSQLQuery("SELECT Item_ID as 'Item ID', Replace(Replace(Item_Name,'$.$',''''),'$..$',',') as 'Item Name', Item_Description as 'Description / Item Number' FROM tbl_Category_Item_File WHERE Catg_ID =" & lstcat.FocusedItem.Text), lstitems, 1)
303 'MsgBox(lstcat.FocusedItem.Text & " - " & lstcat.FocusedItem.SubItems(1).Text)
304 cmbCategory.Text = lstcat.FocusedItem.Text & " - " & lstcat.FocusedItem.SubItems(1).Text
305 If lstitems.Items.Count > 0 Then
306 'lstitems.Focus()
307 sqlSTR = "SELECT * FROM TBL_Category_Item_File WHERE Item_ID =" & lstitems.Items(0).Text
308 ExecuteSQLQuery(sqlSTR)
309 If sqlDT.Rows.Count > 0 Then
310 txtitemID.Text = sqlDT.Rows(0)("Item_ID")
311 txtItemName.Text = R_Change(sqlDT.Rows(0)("Item_Name"))
312 txtItemDesc.Text = sqlDT.Rows(0)("Item_Description")
313 txtItemBarcode.Text = sqlDT.Rows(0)("Item_Barcode")
314 txtItemprice.Text = sqlDT.Rows(0)("Item_Org_Price")
315 cmbUnit.SelectedItem = (sqlDT.Rows(0)("Unit_Measure"))
316 'cmbsupplier.SelectedItem = sqlDT.Rows(0)("Supp_ID") & " - " & sqlDT.Rows(0)("Suppname")
317 txtItemreorder.Text = sqlDT.Rows(0)("Item_Reorder_Point")
318 End If
319
320
321 Else
322 txtid.Text = ""
323 txtItemName.Text = ""
324 txtItemDesc.Text = ""
325 txtItemBarcode.Text = ""
326 txtItemprice.Text = ""
327 txtItemreorder.Text = ""
328 End If
329 End Sub
330
331 Private Sub lstcat_GotFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles lstcat.GotFocus
332 ' add2, edit2 visible = false
333 ' xlstcat = True
334 ' xlstitem = False
335 End Sub
336
337 Private Sub lstcat_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstcat.SelectedIndexChanged
338
339 End Sub
340
341 Private Sub cmdclose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdclose.Click
342 Me.Close()
343 End Sub
344
345 Private Sub cmdEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdEdit.Click
346 ' MsgBox(xlstcat & " " & xlstitem)
347 ' If xlstcat Then
348 If lstcat.Items.Count > 0 Then
349 lstcat.Focus()
350 txtid.Text = lstcat.FocusedItem.Text
351 txtname.Text = lstcat.FocusedItem.SubItems(1).Text
352 txtdesc.Text = lstcat.FocusedItem.SubItems(2).Text
353 edit = True
354 GroupBox1.Enabled = False
355 GroupBox2.Enabled = False
356 GroupBox3.Enabled = False
357 grpCat.Visible = True
358 End If
359 ' ElseIf xlstitem Then
360 ' If lstitems.Items.Count > 0 Then
361 'lstitems.Focus()
362 'lstitems.Select()
363 'edit = True
364 'GroupBox1.Enabled = False
365 'GroupBox2.Enabled = False
366 'GroupBox3.Enabled = True
367 'End If
368 'End If
369 txtname.Focus()
370 End Sub
371
372 Private Sub cmdadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdadd.Click
373
374 'add2, edit2 visible =false
375 ' If xlstcat Then
376 ' edit = False
377 ' txtname.Text = ""
378 ' txtdesc.Text = ""
379 ' grpCat.Visible = True
380 ' GroupBox1.Enabled = False
381 ' GroupBox2.Enabled = False
382 ' GroupBox3.Enabled = False
383 ' ElseIf xlstitem Then
384 ' edit = False
385 ' lstcat.Focus()
386 ' 'edit = False
387 ' GroupBox3.Enabled = True
388 ' GroupBox2.Enabled = False
389 ' GroupBox1.Enabled = False
390 ' txtitemID.Text = ""
391 ' txtItemName.Text = ""
392 ' txtItemDesc.Text = ""
393 ' txtItemBarcode.Text = ""
394 ' txtItemprice.Text = ""
395 ' txtItemreorder.Text = ""
396 ' End If
397 '' Exit Sub
398 ' edit = False
399 ' txtname.Text = ""
400 ' txtdesc.Text = ""
401 ' grpCat.Visible = True
402 ' GroupBox1.Enabled = False
403 ' GroupBox2.Enabled = False
404 ' GroupBox3.Enabled = False
405 ' 'cmdadd.Enabled = False
406 ' 'cmdEdit.Enabled = False
407
408 txtname.Text = ""
409 txtdesc.Text = ""
410 grpCat.Visible = True
411 edit = False
412 txtname.Focus()
413 End Sub
414
415 Private Sub cmbGroup_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbGroup.SelectedIndexChanged
416 FillListView(ExecuteSQLQuery("SELECT Catg_ID as 'ID', Catg_Name as 'Name', Catg_Description as 'Description' " & _
417 "FROM TBL_Category_File " & _
418 " WHERE Group_ID =" & Split(cmbGroup.Text, " - ")(0)), lstcat, 0)
419 FILLComboBox("SELECT Catg_ID, Catg_Name " & _
420 "FROM TBL_Category_File" & _
421 " WHERE Group_ID =" & Split(cmbGroup.Text, " - ")(0), cmbCategory)
422 lstitems.Items.Clear()
423 txtItemDesc.Text = ""
424 txtItemBarcode.Text = ""
425 txtItemName.Text = ""
426 txtItemprice.Text = 1
427 txtItemreorder.Text = ""
428 txtname.Text = ""
429 cmbCategory.Text = ""
430 End Sub
431
432 Private Sub lstitems_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstitems.SelectedIndexChanged
433
434 End Sub
435
436 Private Sub txtItemName_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtItemName.TextChanged
437 txtItemName.Text = filter_Special_Char(str_Filter(txtItemName, 34, 122, 32, 40))
438 End Sub
439
440 Private Sub txtItemDesc_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtItemDesc.TextChanged
441 txtItemDesc.Text = filter_Special_Char(str_Filter(txtItemDesc, 48, 122, 32, 50))
442 End Sub
443
444 Private Sub txtname_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtname.TextChanged
445 txtname.Text = filter_Special_Char(str_Filter(txtname, 65, 122, 32, 40))
446 End Sub
447
448 Private Sub txtdesc_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtdesc.TextChanged
449 'txtdesc.Text = filter_Special_Char(str_Filter(txtdesc, 65, 122, 32, 40))
450 End Sub
451 End Class
